package com.jyh.homework1;

import com.jyh.homework1.domain.Stu;
import com.jyh.utils.JDBCUtil;
import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @Author JYH
 * @Date 2024/8/22 18:35
 * @Description 作业1
 */
public class School {
    public static final String MYSQL_DRIVER="com.mysql.jdbc.Driver";
    public static final String URL="jdbc:mysql://localhost:3306/school?useUnicode=true&useSSL=false&characterEncoding=UTF8";
    public static final String USERNAME="root";
    public static final String PASSWORD="123456";
    @Test
    public void test1() throws SQLException {
        /*
        （1）在控制台输入班级信息，并将数据插入classroom表，需要插入3条数据：
        class_id：1   class_name：普通班
        class_id：2   class_name：进阶班
        class_id：3   class_name：冲刺班
         */
        //1.加载MySQL数据库驱动 2.获取数据库连接
        Connection connection = JDBCUtil.getConnection();
        //3.获取发送和执行sql语句的对象
        Statement statement = connection.createStatement();
        //4.定义sql语句
        String sql="INSERT INTO class value\n" + "(1,'普通班'),\n" + "(2,'进阶班'),\n" + "(3,'冲刺班')";
        //5.使用发送和执行sql语句的对象statement来获取结果：影响的行数
        int count = statement.executeUpdate(sql);
        //6.处理结果
        System.out.println(count==3?"添加成功!":"添加失败!");
        //7.关闭资源
        statement.close();
        connection.close();
    }

    @Test
    public void test2() throws ClassNotFoundException, SQLException {
        /*
        （2）在控制台输入学生信息，并将数据插入student表，需要插入5条数据。
            stu_name：张三 age：18 weight：90 address：北京 class_id：1
            stu_name：李四 age：19 weight：80 address：北京 class_id：1
            stu_name：王五 age：28 weight：50 address：上海 class_id：2
            stu_name：刘六 age：23 weight：70 address：武汉 class_id：2
            stu_name：牛七 age：38 weight：30 address：天津 class_id：3
         */
        //1.加载驱动
        Class.forName(MYSQL_DRIVER);
        //2.建立连接
        Connection connection = DriverManager.getConnection(URL,USERNAME,PASSWORD);
        //3.获取发送和执行的sql对象
        Statement statement = connection.createStatement();
        //4.定义sql语句
        String sql="INSERT INTO stu VALUE('张三',18,90,'北京',1),('李四',19,80,'北京',1),('王五',28,50,'上海',2),('赵六',23,70,'武汉',2),('牛七',38,30,'天津',3)";
        //5.使用statement对象影响的行数
        int count = statement.executeUpdate(sql);
        //6.结果
        System.out.println(count==5?"插入成功!":"插入失败!");
        //7.关闭
        statement.close();
        connection.close();
    }

    @Test
    public  void test3() throws SQLException {
        //编写一个java方法，能够查询打印显示出所有的学生信息
        Connection connection = JDBCUtil.getConnection();
        Statement statement = connection.createStatement();
        String sql="SELECT * FROM stu";
        ResultSet rs = statement.executeQuery(sql);
        while (rs.next()){
            String sname = rs.getString("sname");
            int age = rs.getInt("age");
            int weight = rs.getInt("weight");
            String address = rs.getString("address");
            int classId = rs.getInt("classId");
            System.out.println(sname+"--"+age+"--"+weight+"--"+address+"--"+classId);
        }
        rs.close();
        statement.close();
        connection.close();
    }

    @Test
    public void test4() throws SQLException {
        /*
        编写一个java方法，可以根据传入的page（页码）,count（每页条数）两个参数分页查询打印出学生信息
         */
        /*
           分析：
               pageNum         pageSize         当前这一页数据开始的索引
               1                 3                    0
               2                 3                    3
               pageNum          pageSize           (pageNum - 1) * pageSize
               limit m , n -----> m 开始索引--->(pageNum - 1) * pageSize   ,    n:每页显示条数--->pageSize
         */

        //(2)定义页码和每页条数
        String page="1";
        String count="2";
        Connection connection = JDBCUtil.getConnection();
        Statement statement = connection.createStatement();
        //limit m , n -----> m 开始索引--->(pageNum - 1) * pageSize   ,    n:每页显示条数--->pageSize ，字符串强转为int
        String sql="select * from stu limit "+(Integer.parseInt(page)-1)*Integer.parseInt(count)+","+Integer.parseInt(count);
        ResultSet rs = statement.executeQuery(sql);
        while (rs.next()){
            String sname = rs.getString("sname");
            int age = rs.getInt("age");
            int weight = rs.getInt("weight");
            String address = rs.getString("address");
            int classId = rs.getInt("classId");
            System.out.println(sname+"--"+age+"--"+weight+"--"+address+"--"+classId);
        }
        rs.close();
        statement.close();
        connection.close();
    }

    @Test
    public void test5() throws SQLException {
        /*
        编写一个java方法，按照学生的年龄进行升序排序查询出来，并将学生信息放到list中，再想办法将学生信息通过年龄进行降序进行输出打印
         */
        Connection connection = JDBCUtil.getConnection();
        Statement statement = connection.createStatement();
        String sql="SELECT * FROM stu ORDER BY age DESC";
        ResultSet rs = statement.executeQuery(sql);
        List<Stu> list=new ArrayList<>();
        while (rs.next()){
            String sname = rs.getString("sname");
            int age = rs.getInt("age");
            int weight = rs.getInt("weight");
            String address = rs.getString("address");
            int classId = rs.getInt("classId");
            list.add(new Stu(sname,age,weight,address,classId));
        }
        //左边-1，右边1,降序是大的在左边
        list.stream().sorted((item1,item2)->item1.getAge()>item2.getAge()?-1:(item1.getAge()<item2.getAge()?1:0)).collect(Collectors.toList()).forEach(System.out::println);
    }

    @Test
    public void test6() throws SQLException {
        //编写一个java方法，打印出每个学生的姓名，年龄，以及对应的班级的名称信息
        Connection connection = JDBCUtil.getConnection();
        Statement statement = connection.createStatement();
        String sql="SELECT t2.sname,t2.age,t1.className FROM class t1 JOIN stu t2 on t1.classId=t2.classId";
        ResultSet rs = statement.executeQuery(sql);
        while (rs.next()){
            String sname = rs.getString("sname");
            int age = rs.getInt("age");
            String className = rs.getString("className");
            System.out.println(sname + "--" + age + "--" + className);
        }
        rs.close();
        statement.close();
        connection.close();
    }

    @Test
    public void test7() throws SQLException {
        //编写一个java方法，打印出每个班级的名称和该班级的人数
        Connection connection = JDBCUtil.getConnection();
        Statement statement = connection.createStatement();
        String sql="SELECT t1.className,t2.num FROM class t1 JOIN (SELECT classId,COUNT(*) num from stu GROUP BY classId) t2 ON t1.classId=t2.classId";
        ResultSet rs = statement.executeQuery(sql);
        while (rs.next()){
            String className = rs.getString("className");
            int num = rs.getInt("num"); //等价于--->int num = rs.getInt(2);
            System.out.println(className + "--" + num);
        }
        rs.close();
        statement.close();
        connection.close();
    }
}
